using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using BCB.IDAL;
using BCB.Model;
using BCB.DBUtility;
namespace BCB.SQLServerDAL
{
    public class Product : IProduct
    {
        #region IProduct Members
        private const string SELECT_PRODUCT_BY_PRDID = "";
        private const string SELECT_PRODUCTS_BY_CATEGROYID = "SELECT ProductName FROM [NewBCB].[dbo].[Product] WHERE ProductId = 1 or ProductId =2";
        private const string SELECT_PRODUCTS_COUNT_BY_CATEGORYID = "SELECT COUNT(Id) FROM Product INNER JOIN ProductCategory on Product.Id = ProductCategory.ProductId where ProductCategory.CategoryId = @CategoryId";
        public ProductDetail GetProductByPrdId(int prdId)
        {
            using (SqlDataReader dr = SqlHelper.ExecuteReader(BCB.DBUtility.SqlHelper.BCBConnectionString, CommandType.Text, SELECT_PRODUCT_BY_PRDID))
            {
                return getProductFromDR(dr);
            }

        }
        public List<ProductDetail> GetPrdsByCateId(int categoryId, int pageNum, int pageSize)
        {
            List<ProductDetail> Products = new List<ProductDetail>();
            int startRowIndex = (pageNum - 1) * pageSize + 1;
            SqlParameter[] paras = new SqlParameter[3];
            paras[0] = new SqlParameter("@CategoryId", categoryId);
            paras[1] = new SqlParameter("@PageSize", pageSize);
            paras[2] = new SqlParameter("@RowStartIndex", startRowIndex);
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.BCBConnectionString, CommandType.StoredProcedure, "GetProductsByCategoryAndRecordNum", paras))
            {
                while (dr.Read())
                {
                    Products.Add(getProductFromDR(dr));
                }
            }
            return Products;
        }

        public int GetProductCountByCategoryId(int categoryId)
        {
            SqlParameter para = new SqlParameter("@CategoryId", categoryId);
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.BCBConnectionString, CommandType.Text, SELECT_PRODUCTS_COUNT_BY_CATEGORYID, para))
            {
                int productCount = 0;
                while (dr.Read())
                {
                    productCount = dr.GetInt32(0);
                }
                return productCount;
            }
        }

        #endregion
        private ProductDetail getProductFromDR(SqlDataReader dr)
        {
            SafeDataReader sdr = new SafeDataReader(dr);

            ProductDetail product = new ProductDetail();
            product.Id = sdr.GetInt32("Id");
            product.Name = sdr.GetString("Name");
            product.SKU = sdr.GetString("SKU");
            product.Description = sdr.GetString("Description");
            product.ImageUrl = sdr.GetString("ImageUrl");
            product.ImageUrl2 = sdr.GetString("ImageUrl2");
            product.InkColorID = sdr.GetInt32("InkColorID");
            product.TypeStypeID = sdr.GetInt32("TypeStypeID");
            product.CardOrientation = sdr.GetBoolean("CardOrientation");
            product.PrintedEnvelops = sdr.GetString("PrintedEnvelops");
            product.EmailProof = sdr.GetString("EmailProof");
            product.PaperColor = sdr.GetString("PaperColor");
            product.Deleted = sdr.GetBoolean("Deleted");
            product.AssociatedProduct = sdr.GetString("AssociatedProduct");
            product.Bubble = sdr.GetBoolean("Bubble");
            product.Order = sdr.GetInt32("Order");
            product.CharacterPicture = sdr.GetString("CharacterPicture");

            //ProductDetail product = new ProductDetail();
            //product.Id = sdr.GetInt32(0);
            //product.Name = sdr.GetString(1);
            //product.SKU = sdr.GetString(2);
            //product.Description = sdr.GetString(3);
            //product.ImageUrl = sdr.GetString(4);
            //product.ImageUrl2 = sdr.GetString(5);
            //product.InkColorID = sdr.GetInt32(6);
            //product.TypeStypeID = sdr.GetInt32(7);
            //product.CardOrientation = sdr.GetBoolean(8);

            //product.PrintedEnvelops = sdr.GetString(9);
            //product.EmailProof = sdr.GetString(10);
            //product.PaperColor = sdr.GetString(11);
            //product.Deleted = sdr.GetBoolean(12);
            //product.AssociatedProduct = sdr.GetString(13);
            //product.Bubble = sdr.GetBoolean(14);
            //product.Order = sdr.GetInt32(15);
            //product.CharacterPicture = sdr.GetString(16);
            return product;
        }
    }
}
